package com.cd.works.domain;

import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.fileupload.FileItem;



public class Model {
	public String register(RegBean rb){
		String email=rb.getEmail();
		String userName=rb.getUserName();
		String password=rb.getPassword();
		String mobileNum=rb.getMobileNum();
		
		Connection con=null;
		PreparedStatement ps1=null,ps2=null;
		ResultSet rs=null;
		
		try {
			con=JDBCHelper.getConnection();
			if(con!=null)
			{
				ps1=con.prepareStatement("select * from users where EMAIL=? or MOBILENUM=?");
				ps1.setString(1, email);
				ps1.setString(2, mobileNum);
				rs=ps1.executeQuery();
				if(rs.next())
				{
					return "DUPLICATE";
				}
				else
				{
					System.out.println("Business validation succeded");
					ps2=con.prepareStatement("insert into users(EMAIL, USERNAME, PASSWORD, MOBILENUM,VERIFY) values(?,?,?,?,?)");
					ps2.setString(1, email);
					ps2.setString(2, userName);
					ps2.setString(3, password);
					ps2.setString(4, mobileNum);
					ps2.setBoolean(5, false);
					ps2.execute();
					System.out.println("values inserted");
					JDBCHelper.close(con);
					JDBCHelper.close(rs);
					JDBCHelper.close(ps1);
					JDBCHelper.close(ps2);
					return "SUCCESS";
				}
			}
			else{
				return "DATABASE_CONNECTION_PROBLEM_CONTACT_ADMIN";
			}
			
				
			
		} catch (SQLException e) {
			return "DATABASE_CONNECTION_PROBLEM_CONTACT_ADMIN "+e.getMessage();
		}
		
	}

	public String verifyByCode(String emailId){
		String email=emailId;
		
		Connection con=null;
		PreparedStatement ps1=null, ps2=null;
		ResultSet rs=null;
		try {
			con=JDBCHelper.getConnection();
			if(con!=null)
			{
				ps1=con.prepareStatement("select * from users where EMAIL=?");
				ps1.setString(1, email);
				
				rs=ps1.executeQuery();
				if(rs.next())
				{
					System.out.println("Business validation succeded");
					ps2=con.prepareStatement("update users set verify=? where email=?");
					ps2.setBoolean(1, true);
					ps2.setString(2, email);
					ps2.execute();
					System.out.println("values inserted");
					JDBCHelper.close(con);
					JDBCHelper.close(rs);
					JDBCHelper.close(ps1);
					JDBCHelper.close(ps2);
					return "SUCCESS";
				}
				else
				{
					
					return "INVALID_USER";
				}
			}else{
					return "Databse Connection Problem. Contact admin";
				}
		} catch (Exception e) {
			return e.getMessage();
		}
			
		} 
	
	
	public String login(LogBean lb){
		Connection con=null;
		PreparedStatement ps1=null;
		ResultSet rs=null;
		String password=lb.getPassword();
		try {
			long mob=Long.parseLong(lb.getMobEmail());
			
			try {
				con=JDBCHelper.getConnection();
				if (con!=null) {
					ps1=con.prepareStatement("select * from users where MOBILENUM=? and password=?");
					ps1.setString(1, String.valueOf(mob));
					ps1.setString(2, password);
					rs=ps1.executeQuery();
					if(rs.next())
					{
						return "SUCCESS";
					}else {
						return "INVALID_USER";
					}
				}else {
					return "DB Connection Problem";
				}
				
			} catch (SQLException e) {
				return e.getMessage();
			}
		} catch (NumberFormatException nfe) {
			String email=lb.getMobEmail();
			try {
				con=JDBCHelper.getConnection();
				if (con!=null) {
					ps1=con.prepareStatement("select * from users where EMAIL=? and password=?");
					ps1.setString(1, email);
					ps1.setString(2, password);
					rs=ps1.executeQuery();
					if(rs.next())
					{
						return "SUCCESS";
					}else {
						return "INVALID_USER";
					}
				}else {
					return "DB Connection Problem";
				}
				
			} catch (SQLException e) {
				return e.getMessage();
			}
		}
		
		
	}

	public int upload(FileItem fileUpload, String fileName) {
		System.out.println("Inside upload(..) of Model");
		
		Connection con=JDBCHelper.getConnection();
		PreparedStatement ps=null;
		int num = 0;
		try {
			if (con != null) {
			con.setAutoCommit(false);
			String query = "insert into file_table(id, file_data, file_name) values(?,?,?) ";
			ps= con.prepareStatement(query);
			 /* Sets the designated parameter to the given input stream,
			which will have the specified number of bytes*/
			ps.setInt(1, 1);
			ps.setBinaryStream(2, fileUpload.getInputStream(),(int)fileUpload.getSize());
			ps.setString(3, fileName);
			num = ps.executeUpdate();
			System.out.println("num: "+num);
			if (num == 1) {
			System.out.println("File upload is successful");
			} else {
			System.out.println("File upload failure");
			}
			} else {
			System.out.println("DB Connection Fails");
			}
			} 
		 catch (Exception e) {
			 System.out.println("error while uploading file: "+e.getMessage());
			 e.printStackTrace();
			 }
		finally
		{
		JDBCHelper.close(con);
		JDBCHelper.close(ps);
		}
		return num;


	}

	public int uploadImage(InputStream inputStream) {
System.out.println("Inside upload(..) of Model");
int row=0;
		Connection con=JDBCHelper.getConnection();
		PreparedStatement ps=null;
		try {
			if (con != null) {
			con.setAutoCommit(false);
			String query = "INSERT INTO contacts (first_name, last_name, photo) values (?, ?, ?)";
			ps= con.prepareStatement(query);
			 /* Sets the designated parameter to the given input stream,
			which will have the specified number of bytes*/
			ps.setString(1, "vinod");
			ps.setString(2, "kumar");
			
			if (inputStream != null) {
                // fetches input stream of the upload file for the blob column
				ps.setBlob(3,inputStream);
            }
			
			
			row = ps.executeUpdate();
			System.out.println("num: "+row);
			if (row==1) {
                System.out.println("File uploaded and saved into database");
            } else {
			System.out.println("File upload failure");
			}
			} else {
			System.out.println("DB Connection Fails");
			}
			} 
		 catch (Exception e) {
			 System.out.println("error while uploading file: "+e.getMessage());
			 e.printStackTrace();
			 }
		finally
		{
		JDBCHelper.close(con);
		JDBCHelper.close(ps);
		}
		return row;
	}

	public byte[] retriveImage(String email) {
		Connection con=JDBCHelper.getConnection();
		PreparedStatement ps=null;
		Blob blob=null;
		
		ResultSet rs=null;
		try {
			if (con != null) {
			System.out.println("connection established");
			String query = "select data from users where email=?";
			ps= con.prepareStatement(query);
			 /* Sets the designated parameter to the given input stream,
			which will have the specified number of bytes*/
			ps.setString(1, "vinodkumarbm.dvg@gmail.com");
			
			
			
			rs = ps.executeQuery();
			System.out.println("Executing query");
			if(rs.next()){
				blob=rs.getBlob(1);
				System.out.println("Blob is :"+blob);
				byte[] barr=blob.getBytes(1, (int)blob.length());
				System.out.println("this is blob array :"+barr);
				return barr;
			}else{
				return null;
			}
			} else {
			System.out.println("DB Connection Fails");
			return null;
			}
			} 
		 catch (Exception e) {
			 System.out.println("error while uploading file: "+e.getMessage());
			 e.printStackTrace();
			 return null;
			 }
		finally
		{
		JDBCHelper.close(con);
		JDBCHelper.close(ps);
		}
		
	}

	public String setImage(InputStream inputStream) {
		
		Connection con=JDBCHelper.getConnection();
		PreparedStatement ps=null;
		
		ResultSet rs=null;
		try {
			if (con != null) {
			System.out.println("connection established");
			ps=con.prepareStatement("insert into IMGTABLE values(?,?)");
			ps.setString(1,"vinod");  
			  
//			FileInputStream fin=new FileInputStream("d:\\vinod.jpg");  
//			ps.setBinaryStream(2,fin,fin.available());  
//			int i=ps.executeUpdate();  
			if (inputStream != null) {  
                // fetches input stream of the upload file for the blob column  
                ps.setBlob(2, inputStream);  
            }  
//			System.out.println(i+" records affected"); 
			return "SUCCESS";
			
			} else {
			System.out.println("DB Connection Fails");
			return null;
			}
			} 
		 catch (Exception e) {
			 System.out.println("error while uploading file: "+e.getMessage());
			 e.printStackTrace();
			 return null;
			 }
		finally
		{
		JDBCHelper.close(con);
		JDBCHelper.close(ps);
		}		
	}

	public String test(String name) {
		System.out.println("Getting Connection");
		Connection con=JDBCHelper.getConnection();
		PreparedStatement ps=null;
		
		try{
			if(con!=null){
				System.out.println("DB Connection Success");
				ps=con.prepareStatement("INSERT INTO CUSTOMERS(name) VALUES(?)");
				ps.setString(1, "vinod");
				ps.execute();
				System.out.println("Values insertes");
				return "SUCCESS";
			}else {
				return "FAILURE";
			}
		}
		 catch (Exception e) {
			 System.out.println("error while uploading file: "+e.getMessage());
			 e.printStackTrace();
			 return null;
			 }
		finally
		{
		JDBCHelper.close(con);
		JDBCHelper.close(ps);
		}		
	}

}

